Skip Headers

Oracle® Data Guard Broker 10g Release 2 (10.2) Part Number B14230-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 7 Scenarios Using the DGMGRL Command-Line Interface

This chapter provides several scenarios that show how to use the Data Guard command-line interface (DGMGRL) to create, manage, and monitor a broker configuration.

In addition to the prerequisites for getting started, this chapter describes the following scenarios:

- Scenario 1: Creating a Configuration

- Scenario 2: Setting Database Properties

- Scenario 3: Enabling the Configuration and Databases

- Scenario 4: Setting the Configuration Protection Mode

- Scenario 5: Enabling Fast-Start Failover and the Observer

- Scenario 6: Performing Routine Management Tasks

- Scenario 7: Performing a Switchover Operation

- Scenario 8: Performing a Manual Failover Operation

- Scenario 9: Monitoring a Data Guard Configuration

## 7.1 Prerequisites for Getting Started

One of the prerequisites for using DGMGRL is that a primary database and any standby databases must already exist. The `DG_BROKER_START` initialization parameter must be set to `TRUE` for all databases in the configuration. You must use a server parameter file with the broker (see Section 2.1 and Section 8.1.3).

Convert the initialization parameter files (PFILE) on both primary and standby databases into server parameter files (SPFILE), if necessary. Use the following SQL*Plus command:

    CREATE SPFILE FROM PFILE='pfilename';

If an instance was not started with a server parameter file, then you must shut down the instance and restart it using the server parameter file.

After starting the Oracle instance, set the `DG_BROKER_START=TRUE` initialization parameter using the SQL `ALTER SYSTEM` statement. The parameter value will be saved in the server parameter file. The next time you start the Oracle instance, the broker is started automatically, and you do not need to issue the SQL `ALTER SYSTEM` statement again.

See Also: Oracle Database Administrator's Guide for detailed information about creating server parameter files

The following assumptions are made for the scenarios in this chapter:

- TCP/IP is used to connect to primary and standby databases.

- The standby database has been created from backups of the primary database control files and datafiles as described in the Oracle Data Guard Concepts and Administration

- The scenarios in this chapter assume the following broker configuration:

  - The configuration name is `DRSolution`.

  - The database unique name (`DB_UNIQUE_NAME`) of the primary database is `North_Sales`.

  - The database unique name (`DB_UNIQUE_NAME`) of the remote standby database is `DR_Sales`.

  - The protection mode is maximum performance mode.

  - There are no standby redo log files.

  - The standby database is a physical standby database.

## 7.2 Scenario 1: Creating a Configuration

This section provides examples that create a broker configuration named `DRSolution` that includes a primary and standby database named `North_Sales` and `DR_Sales`.

The following steps show how to create a configuration and add one physical standby database.

Step 1 Invoke DGMGRL.

To start DGMGRL, enter `DGMGRL` at the command-line prompt on a system where Oracle Data Guard is installed:

    $ dgmgrl

    DGMGRL for Linux: Version 10.2.0.1.0 - ProductionCopyright (c) 2000, 2005, Oracle. All rights reserved.Welcome to DGMGRL, type "help" for information.

    DGMGRL>

Step 2 Connect to the primary database.

Before you specify any command (other than the `HELP,` `EXIT,` or `QUIT`), you must first connect to the primary database using the DGMGRL `CONNECT` command.

The account from which you connect to the database (`SYS` in this example) must have `SYSDBA` privileges on the primary and standby databases.

Note: You do not have to include `AS SYSDBA` on the `CONNECT` command because `SYSDBA` is the default setting for this command.

The following examples show two variations of the `CONNECT` command. Example 7-1 shows how to connect to the default database on the local system, and Example 7-2 includes the Oracle Net Services connect identifier (`North_Sales.foo.com`) to make a connection to a database located on a remote system.

Example 7-1 Connecting to the Primary Database on the Local System

    DGMGRL> CONNECT sys/<password>;
    Connected.

Example 7-2 Connecting to the Primary Database on a Remote System

    DGMGRL> CONNECT sys/<password>@North_Sales.foo.com;
    Connected.

Optionally, you can combine the steps shown in Example 7-1 and Example 7-2 by including the connection information on the command line:

    $ DGMGRL sys/<password>@<connect identifier>

Step 3 Create the broker configuration.

To create the broker configuration, you first define the configuration including a profile for the primary database, which in this case is called `North_Sales`. In a later command, you will add a profile for the standby database, `DR_Sales`.

Note: The names for the primary and standby databases must match their database unique names. Fetch these from their `DB_UNIQUE_NAME` initialization parameter as follows: `SQL> SHOW PARAMETER DB_UNIQUE_NAME;`

Use the `CREATE CONFIGURATION` command to create the `DRSolution` configuration and define the primary database, `North_Sales`:

    DGMGRL> CREATE CONFIGURATION 'DRSolution' AS
    >  PRIMARY DATABASE IS 'North_Sales'
    >  CONNECT IDENTIFIER IS North_Sales.foo.com;

DGMGRL returns the following information:

    Configuration "DRSolution" created with primary database "North_Sales"

The name `North_Sales` is the value of this database's `DB_UNIQUE_NAME` initialization parameter.

Step 4 Show the configuration information.

Use the `SHOW CONFIGURATION` command to display a brief summary of the configuration:

    DGMGRL> SHOW CONFIGURATION;

DGMGRL returns the following information:

    Configuration
     Name:                DRSolution
     Enabled:             NO
     Protection Mode:     MaxPerformance
     Fast-Start Failover: DISABLED
     Databases:
       North_Sales  - Primary database

    Current status for "DRSolution":
    DISABLED

Step 5 Add a standby database to the configuration.

To add a standby database to the `DRSolution` configuration, use the `ADD DATABASE` command to create a broker configuration profile for the standby database.

The following command defines `DR_Sales` as a standby database, which is the standby database associated with the primary database called `North_Sales`:

    DGMGRL> ADD DATABASE 'DR_Sales' AS
    >  CONNECT IDENTIFIER IS DR_Sales.foo.com
    >  MAINTAINED AS PHYSICAL;

DGMGRL returns the following information:

    Database "DR_Sales" added

The name `DR_Sales` is the value of the database's `DB_UNIQUE_NAME` initialization parameter.

Use the `SHOW CONFIGURATION` command to verify that the `DR_Sales` database was added to the `DRSolution` configuration:

    DGMGRL> SHOW CONFIGURATION;

DGMGRL returns the following information:

    Configuration
     Name:                DRSolution
     Enabled:             NO
     Protection Mode:     MaxPerformance
     Fast-Start Failover: DISABLED
     Databases:
       North_Sales  - Primary database
       DR_Sales     - Physical standby database

    Current status for "DRSolution":
    DISABLED

## 7.3 Scenario 2: Setting Database Properties

After you create the configuration with DGMGRL, you can set database properties at any time. For example, the following statements set the `LogArchiveFormat` and `StandbyArchiveLocation` properties for the `DR_Sales` standby database:

    DGMGRL> EDIT DATABASE 'DR_Sales' SET PROPERTY 'LogArchiveFormat'='log_%t_%s_%r_%d.arc';
    Property "LogArchiveFormat" updated.

    DGMGRL> EDIT DATABASE 'DR_Sales' SET PROPERTY 'StandbyArchiveLocation'='/archfs/arch/';
    Property "StandbyArchiveLocation" updated.

Use the `SHOW DATABASE VERBOSE` command to view all properties and their values for a database. The following example shows the properties for the `DR_Sales` database.

    DGMGRL> SHOW DATABASE VERBOSE 'DR_Sales';

    Database
     Name:            DR_Sales
     Role:            PHYSICAL STANDBY
     Enabled:         NO
     Intended State:  ONLINE
     Instance(s):
       DR_Sales1

     Properties:
       InitialConnectIdentifier        = 'DR_Sales.foo.com'
       LogXptMode                      = 'ARCH'
       Dependency                      = ''
       DelayMins                       = '0'
       Binding                         = 'OPTIONAL'
       MaxFailure                      = '0'
       MaxConnections                  = '0'
       ReopenSecs                      = '300'
       NetTimeout                      = '180'
       LogShipping                     = 'ON'
       PreferredApplyInstance          = ''
       ApplyInstanceTimeout            = '0'
       ApplyParallel                   = 'AUTO'
       StandbyFileManagement           = 'AUTO'
       ArchiveLagTarget                = '0'
       LogArchiveMaxProcesses          = '5'
       LogArchiveMinSucceedDest        = '1'
       DbFileNameConvert               = 'dbs/t, dbs/s2t'
       LogFileNameConvert              = 'dbs/t, dbs/s2t'
       FastStartFailoverTarget         = ''
       StatusReport                    = '(monitor)'
       InconsistentProperties          = '(monitor)'
       InconsistentLogXptProps         = '(monitor)'
       SendQEntries                    = '(monitor)'
       LogXptStatus                    = '(monitor)'
       RecvQEntries                    = '(monitor)'
       HostName                        = 'north.foo.com'
       SidName                         = 'dr_sales1'
       LocalListenerAddress            = '(ADDRESS=(PROTOCOL=TCP)(HOST=dr.foo.com)(PORT=1514))'
       StandbyArchiveLocation          = '/archfs/arch'
       AlternateLocation               = ''
       LogArchiveTrace                 = '255'
       LogArchiveFormat                = 'log_%t_%s_%r_%d.arc'
       LatestLog                       = '(monitor)'
       TopWaitEvents                   = '(monitor)'

    Current status for "DR_Sales":
    DISABLED

If broker management of the database is enabled, setting a database property value causes the underlying parameter value to be changed in the corresponding database, and the value for the changed parameter is reflected in the server parameter file. Thus, if the database is shut down and restarted outside of Oracle Enterprise Manager and DGMGRL (such as from the SQL*Plus interface), the database uses the new parameter values from the updated server parameter file when it starts. However, you should not make changes to the redo transport services initialization parameters through SQL statements. Doing so will cause an inconsistency between the database and the broker.

Note: The database properties are typically displayed in mixed-case (for example, `LogArchiveFormat`) typeface to help you visually differentiate database properties (from the corresponding initialization parameter, SQL statement, or PL/SQL procedure), which are typically documented in `UPPERCASE` typeface. However, the commands to manage properties are not case sensitive; you can issue commands in uppercase, lowercase, or mixed-case.

You can change a property if the database is enabled or disabled. However, if the database is disabled when you change a property, the change does not take effect until the database is enabled.

## 7.4 Scenario 3: Enabling the Configuration and Databases

So far, the `DRSolution` configuration is disabled, which means it is not under the control of the Data Guard broker. When you finish configuring the databases into a broker configuration and setting any necessary database properties, you must enable the configuration to allow the Data Guard broker to manage it. This brings the primary and standby databases online.

You can enable:

- The entire configuration, including all of its databases

- A standby database

Enable the entire configuration.

You can enable the entire configuration, including all of the databases, with the following command:

    DGMGRL> ENABLE CONFIGURATION;
    Enabled.

Show the configuration.

Use the `SHOW` command to verify that the configuration and its databases were successfully enabled and brought online:

    DGMGRL> SHOW CONFIGURATION;

DGMGRL returns the following information:

    Configuration
     Name:                DRSolution
     Enabled:             YES
     Protection Mode:     MaxPerformance
     Fast-Start Failover: DISABLED
     Databases:
       North_Sales  - Primary database
       DR_Sales     - Physical standby database

    Current status for "DRSolution"
    SUCCESS

Enable the database.

This step is unnecessary except if the standby database was previously disabled with the `DISABLE DATABASE` command. Normally, enabling the configuration also enables the standby database.

    DGMGRL> ENABLE DATABASE 'DR_Sales';
    Enabled.

Show the database.

    DGMGRL> SHOW DATABASE 'DR_Sales';

    Database
     Name:            DR_Sales
     Role:            PHYSICAL STANDBY
     Enabled:         YES
     Intended State:  ONLINE
     Instance(s):
       DR_Sales1

    Current status for "DR_Sales":
    SUCCESS

## 7.5 Scenario 4: Setting the Configuration Protection Mode

You can change the protection mode of the configuration at any time. However, it is best if you do this when there is no activity occurring in the configuration if you are moving to the maximum protection or maximum availability modes of data protection.

Note: If the protection mode to be set is higher than what is currently set in the configuration, the broker automatically restarts the primary database.

This scenario sets the protection mode of the configuration to the `MAXPROTECTION` mode. Note that this protection mode requires that there be at least one standby database configured to use standby redo log files.

Step 1 Configure standby redo log files, if necessary.

Because you will be setting the protection mode to the `MAXPROTECTION` mode, it is important to ensure that sufficient standby redo log files are configured on the standby database.

See Also: Oracle Data Guard Concepts and Administration for information about creating standby redo log files

Step 2 Set the LogXptMode property appropriately.

Use the `EDIT DATABASE (property)` command on the standby database to set the redo transport service that corresponds to the protection mode you plan to set. If the protection mode to be set is `MAXPROTECTION`, it is required that the redo transport service of at least one standby database is set to `SYNC`. For example:

    DGMGRL> EDIT DATABASE 'DR_Sales' SET PROPERTY 'LogXptMode'='SYNC';
    Property "LogXptMode" updated

The broker will not allow this command to succeed unless the standby database is configured with standby redo log files in the configuration.

Step 3 Change the overall protection mode for the configuration.

Use the `EDIT` `CONFIGURATION` command to upgrade the broker configuration to the `MAXPROTECTION` protection mode:

    DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;
    Operation requires shutdown of instance "sales1" on database "North_Sales"

    Shutting down instance "sales"...
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    Operation requires startup of instance "sales1" on database "North_Sales"
    Starting instance "sales1"...
    ORACLE instance started.
    Database mounted.

After you change the protection mode, the primary database instances automatically restart.

If the configuration is disabled when you enter this command, the actual protection mode change is not applied until you enable the configuration with the `ENABLE CONFIGURATION` command. The broker will not allow you to enable the configuration if it does not find a standby database in the configuration that can support the requirements of the protection mode.

Step 4 Verify the protection mode was changed.

Use the `SHOW CONFIGURATION` command to display the current protection mode for the configuration:

    Configuration
     Name:                DRSolution
     Enabled:             YES
     Protection Mode:     MaxProtection
     Fast-Start Failover: DISABLED
     Databases:
       North_Sales  - Primary database
       DR_Sales     - Physical standby database

    Current status for "DRSolution"
    SUCCESS

See Also: Section 4.6, "Managing Data Protection Modes"

## 7.6 Scenario 5: Enabling Fast-Start Failover and the Observer

You can enable fast-start failover from any site, including the observer site, while connected to any database in the broker configuration. Enabling fast-start failover does not trigger a failover. Instead, it allows the observer to begin observing the primary and standby databases and initiate a fast-start failover should conditions warrant a failover.

This section describes the steps to enable fast-start failover and the observer:

- Ensure standby redo logs are configured on all databases.

- Ensure the LogXptMode Property is set to SYNC.

- Specify the FastStartFailoverTarget property.

- Upgrade the protection mode to `MAXAVAILABILITY`, if necessary.

- Enable Flashback Database on the primary and standby databases, if necessary.

- Enable fast start failover.

- Start the observer.

- Verify the fast-start failover configuration.

Step 1 Ensure standby redo logs are configured on all databases.

Standby redo logs must be configured on the primary and standby databases. You must stop log apply services prior to configuring standby redo logs.

See Also: Oracle Data Guard Concepts and Administration for instructions on configuring standby redo log files

Step 2 Ensure the LogXptMode Property is set to SYNC.

The `LogXptMode` property must be set to `SYNC` on the primary and target standby databases.

To set the redo transport service that corresponds to the protection mode you plan to set, use the `EDIT DATABASE` (property) command on the primary and target standby databases. For example, if the protection mode to be set is `MAXPROTECTION`, you must set the `LogXptMode` property to `SYNC` on the primary database and on at least one standby database, as shown in the following examples:

    DGMGRL> EDIT DATABASE 'North_Sales' SET PROPERTY 'LogXptMode'='SYNC';
    Property "LogXptMode" updated

    DGMGRL> EDIT DATABASE 'DR_Sales' SET PROPERTY 'LogXptMode'='SYNC';
    Property "LogXptMode" updated

The broker does not allow these commands to succeed unless the databases are configured with standby redo log files.

Step 3 Specify the F astStartFailoverTarget property.

If you have two or more standby databases, set up the `FastStartFailoverTarget` property on the primary database and the target standby database to point to each other. For example:

    DGMGRL> EDIT DATABASE 'North_Sales' SET PROPERTY FastStartFailoverTarget='DR_Sales';
    Property "FastStartFailoverTarget" updated

    DGMGRL> EDIT DATABASE 'DR_Sales' SET PROPERTY FastStartFailoverTarget='North_Sales';
    Property "FastStartFailoverTarget" updated

Step 4 Upgrade the protection mode to `MAXAVAILABILITY`, if necessary.

If it is necessary to upgrade the protection mode, use the following DGMGRL `EDIT CONFIGURATION` command. For example:

    DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

    Operation requires shutdown of instance "sales1" on database "North_Sales"
    Shutting down instance "sales1"...
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    Operation requires startup of instance "sales1" on database "North_Sales"
    Starting instance "sales1"...
    ORACLE instance started.
    Database mounted.

Step 5 Enable Flashback Database on the primary and standby databases, if necessary.

If it is not already enabled on the primary and standby databases, enable Flashback Database by issuing the following statements on each database:

    ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
    ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    SHOW PARAMETER UNDO;
    ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH;
    ALTER DATABASE ARCHIVELOG;
    ALTER DATABASE FLASHBACK ON;
    ALTER DATABASE OPEN;

Ensure the `UNDO_RETENTION` and `DB_FLASHBACK_RETENTION_TARGET` initialization parameters are set to sufficiently large values so that reinstatement is still possible after a prolonged outage.

Step 6 Enable fast start failover.

You can enable fast-start failover while connected to any database system in the broker configuration, including on the observer computer. For example:

    DGMGRL> ENABLE FAST_START FAILOVER;
    Enabled.

Step 7 Start the observer.

Start the observer by logging into the observer computer and running DGMGRL. Connect to the configuration and then issue the `START OBSERVER` command. Note that the command does not return; that is you will not get DGMGRL prompt after issuing the command.

    DGMGRL> CONNECT sys/password@North_Sales.foo.com;
    DGMGRL> START OBSERVER;
    Observer started

When starting the observer interactively, Oracle recommends that connection credentials be supplied as a command parameter to the DGMGRL `CONNECT` command, as shown in the example, rather than as a command line parameter to the DGMGRL command. This practice prevents other users on the system from using a utility (for example, the UNIX `ps` utility) to display the connection credentials.

When starting the observer from a script, Oracle recommends that you use a method that supports connection as '/', so that connection credentials do not have to be embedded within the script. The Oracle Wallet, described in the Oracle Database Advanced Security Administrator's Guide, is one such method. If you choose to use the Oracle Wallet, be sure to add a default password to the wallet and to set the value of this default password to that of the `SYS` password on the primary database.

Step 8 Verify the fast-start failover configuration.

Use the `SHOW CONFIGURATION` command to display the readiness of the fast-start failover configuration:

    DGMGRL> SHOW CONFIGURATION VERBOSE;

    Configuration
     Name:                DRSolution
     Enabled:             YES
     Protection Mode:     MaxAvailability
     Fast-Start Failover: ENABLED
     Databases:
       North_Sales - Primary database
       DR_Sales    - Physical standby database
                   - Fast-Start Failover target

    Fast-Start Failover
     Threshold: 30 seconds
     Observer:  observer.foo.com

    Current status for "DRSolution":
    SUCCESS

In the example, note that the FastStartFailoverThreshold value is 30 seconds. This is the default value.

## 7.7 Scenario 6: Performing Routine Management Tasks

There may be situations in which you want to change the state or properties of the databases in a broker configuration to perform routine maintenance on one or more databases. You might also need to temporarily disable broker management of databases in a configuration.

### 7.7.1 Changing Properties and States

As you monitor the configuration, you might need to dynamically modify the states of the databases or their properties. The following sections show how to change the state or properties of the databases in the configuration.

#### 7.7.1.1 Alter a Database Property

You can modify the values of database properties at any time—if the database is enabled, disabled, online, or offline.

Example 7-3 shows how to use the `EDIT DATABASE` command to change the `LogArchiveTrace` property to the value 127 for the `North_Sales` database.

Example 7-3 Altering a Database Property

    DGMGRL> EDIT DATABASE 'North_Sales' SET PROPERTY 'LogArchiveTrace'='127';

DGMGRL returns the following message to indicate that the `LogArchiveTrace` property was updated successfully in the Data Guard configuration file:

    Property "LogArchiveTrace" updated

If the configuration is currently disabled, the database does not use the new property value until you enable the broker configuration with the `ENABLE CONFIGURATION` command.

#### 7.7.1.2 Alter the State of a Standby Database

You might want to use your physical standby database temporarily for reporting applications. To change the state of the standby database to read-only, enter the `EDIT DATABASE` command as shown in Example 7-4.

Example 7-4 Altering a Standby Database State

    DGMGRL> EDIT DATABASE 'DR_Sales' SET STATE='READ-ONLY';
    Succeeded.

Log files are still being received when you put the physical standby database in the read-only state. The broker stops Redo Apply from applying the redo data to the standby database.

#### 7.7.1.3 Alter the State of a Primary Database

You might want to stop the transmittal of log files to the standby database. To change the state of the primary database to accommodate this, use the `EDIT DATABASE North_Sales SET STATE=LOG-TRANSPORT-OFF` command. You can also set the primary database `OFFLINE`, which effectively shuts down the primary database and disables the broker from managing the configuration (shown in Example 7-5).

Example 7-5 Altering a Primary Database State

    DGMGRL> EDIT DATABASE 'North_Sales' SET STATE='OFFLINE';

DGMGRL returns the following message to indicate the command was successfully executed:

    Operation requires shutdown of instance "sales1" on database "North_Sales"

    Shutting down instance "sales1"...
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

To change the primary state back to `ONLINE`, you must start the primary database.

### 7.7.2 Disabling the Configuration and Databases

When you disable the broker configuration or any of its databases, you are disabling the broker's management of those objects and are effectively removing your ability to use DGMGRL to manage and monitor the disabled object. However, disabling the broker's management of a broker configuration does not affect the actual operation of the underlying Data Guard configuration or the databases. For example, the redo transport services and log apply services in the Data Guard configuration continue to function unchanged, but you can no longer manage them with DGMGRL.

#### 7.7.2.1 Disable a Configuration

You must use the `DISABLE CONFIGURATION` command to disable management of the entire broker configuration including the primary database as shown in Example 7-6.

Example 7-6 Disabling the Configuration and Primary Database

    DGMGRL> DISABLE CONFIGURATION;

The only way to disable broker management of the primary database is to use the `DISABLE CONFIGURATION` command; the `DISABLE DATABASE` command only disables management of a standby database.

Note: If you disable management of a configuration while connected to the standby database, you must connect to the primary database to reenable the configuration.

Disabling the broker's management of an object does not remove or delete its profile from the broker configuration file. You can reenable your ability to use DGMGRL (or Enterprise Manager) to manage the object by entering the appropriate `ENABLE` `CONFIGURATION` or `ENABLE DATABASE` command.

#### 7.7.2.2 Disable a Standby Database

You use the `DISABLE DATABASE` command when you temporarily do not want the broker to manage and monitor a standby database.

You can explicitly disable broker management of a standby database to prevent it from being brought online when the rest of the configuration is brought online. Example 7-7 shows how to disable the `DR_Sales` standby database.

Example 7-7 Disabling a Standby Database

    DGMGRL> DISABLE DATABASE 'DR_Sales';
    Disabled.

Note: If you disable management of a standby database while connected to that standby database, you must connect to the primary database or another enabled standby database to reenable broker-management of the standby database.

Caution: If you disable broker management of a standby database in the broker configuration, that standby database cannot be used by the broker as a failover target in the event of loss of the primary database.

When running in either the maximum protection or maximum availability mode, the broker prevents you from disabling the last standby database that supports the protection mode.

### 7.7.3 Removing the Configuration or a Standby Database

When you use either the `REMOVE CONFIGURATION` or `REMOVE DATABASE` command, you effectively delete the configuration or standby database profile from the broker configuration file, removing the ability of the Data Guard broker to manage the configuration or the standby database, respectively.

A remove operation does not remove or delete the actual Data Guard configuration underneath, nor does it affect the operation of the actual Data Guard configuration and its databases.

Caution: After you use the `REMOVE CONFIGURATION` or `REMOVE DATABASE` command, you cannot recover the configuration or database profile that was deleted from the broker configuration file. You must go through the steps in Section 7.2 as necessary, to create a broker configuration that can be managed with DGMGRL (or the Enterprise Manager).

Step 1 Remove a standby database from the configuration.

When you use the `REMOVE DATABASE` command, broker management and monitoring of the database ceases and the database's profile is deleted from the broker configuration file:

    DGMGRL> SHOW CONFIGURATION;

    Configuration
     Name:                DRSolution
     Enabled:             NO
     Protection Mode:     MaxPerformance
     Fast-Start Failover: DISABLED
     Databases:
       North_Sales  - Primary database
       DR_Sales     - Physical standby database

    Current status for "DRSolution":
    SUCCESS

DGMGRL returns the following message to indicate the command successfully removed the `DR_Sales` database information from the Data Guard configuration file:

    DGMGRL> REMOVE DATABASE 'DR_Sales';
    Removed database "DR_Sales" from the configuration

    DGMGRL> SHOW CONFIGURATION;
    Configuration
     Name:                DRSolution
     Enabled:             NO
     Protection Mode:     MaxPerformance
     Fast-Start Failover: DISABLED
     Databases:
       North_Sales  - Primary database

    Current status for "DRSolution":
    SUCCESS

When running in either the maximum protection or maximum availability mode, the broker prevents you from deleting the last standby database that supports the protection mode.

Step 2 Remove the broker configuration.

Use the following command to remove the entire configuration from management and monitoring by the broker:

    DGMGRL> REMOVE CONFIGURATION;

Note: You cannot remove the configuration if fast-start failover is enabled.

DGMGRL returns the following message to indicate the command successfully removed all of the configuration information from the Data Guard configuration file:

    Removed configuration

    DGMGRL> SHOW CONFIGURATION;
    Error: ORA-16532: Data Guard broker configuration does not exist

    Configuration details cannot be determined by DGMGRL

## 7.8 Scenario 7: Performing a Switchover Operation

You can switch the role of the primary database and a standby database using the `SWITCHOVER` command. Before you issue the `SWITCHOVER` command, you must ensure:

- The state of the primary and standby databases are online.

- All participating databases are in good health, without any errors or warnings present.

- The standby database properties were set on the primary database, so that the primary database can function correctly when transitioning to a standby database (shown in the following examples in boldface type).

- Standby redo log files on the primary database are set up, and the `LogXptMode` property is set to `SYNC` if the configuration protection mode is maximum availability or maximum protection.

- The observer is running if fast-start failover is enabled.

See Also: Oracle Data Guard Concepts and Administration

Step 1 Check the primary database.

Use the `SHOW DATABASE VERBOSE` command to check the state, health, and properties of the primary database, as follows:

    DGMGRL> SHOW DATABASE VERBOSE 'North_Sales'

    Database
     Name:            North_Sales
     Role:            PRIMARY
     Enabled:         YES
     Intended State:  ONLINE
     Instance(s):
       sales1

     Properties:
       InitialConnectIdentifier        = 'North_Sales.foo.com'
       LogXptMode                      = 'SYNC'
       Dependency                      = ''
       DelayMins                       = '0'
       Binding                         = 'OPTIONAL'
       MaxFailure                      = '0'
       MaxConnections                  = '0'
       ReopenSecs                      = '30'
       NetTimeout                      = '180'
       LogShipping                     = 'ON'
       PreferredApplyInstance          = ''
       ApplyInstanceTimeout            = '0'
       ApplyParallel                   = 'AUTO'
       StandbyFileManagement           = 'AUTO'
       ArchiveLagTarget                = '0'
       LogArchiveMaxProcesses          = '5'
       LogArchiveMinSucceedDest        = '1'
       DbFileNameConvert               = 'dbs/s2t, dbs/t'
       LogFileNameConvert              = 'dbs/s2t, dbs/t'
       FastStartFailoverTarget         = ''
       StatusReport                    = '(monitor)'
       InconsistentProperties          = '(monitor)'
       InconsistentLogXptProps         = '(monitor)'
       SendQEntries                    = '(monitor)'
       LogXptStatus                    = '(monitor)'
       RecvQEntries                    = '(monitor)'
       HostName                        = 'north.foo.com'
       SidName                         = 'sales1'
       LocalListenerAddress            = '(ADDRESS=(PROTOCOL=TCP)(HOST=north.foo.com)(PORT=1514))'
       StandbyArchiveLocation          = '/archfs/arch/'
       AlternateLocation               = ''
       LogArchiveTrace                 = '127'
       LogArchiveFormat                = 'r_%d_%t_%s_%r.arc'
       LatestLog                       = '(monitor)'
       TopWaitEvents                   = '(monitor)'

    Current status for "North_Sales":
    SUCCESS

In particular, you should examine the boldface properties and the current status of the primary database. See Chapter 4 for information about managing databases.

Step 2 Check the standby database that is the target of the switchover.

Use the `SHOW DATABASE VERBOSE` command to check the state, health, and properties of the standby database that is the target of the switchover. For example:

    DGMGRL> SHOW DATABASE VERBOSE 'DR_Sales'

    Database
     Name:            DR_Sales
     Role:            PHYSICAL STANDBY
     Enabled:         YES
     Intended State:  ONLINE
     Instance(s):
       dr_sales1

     Properties:
       InitialConnectIdentifier        = 'DR_Sales.foo.com'
       LogXptMode                      = 'SYNC'
       Dependency                      = ''
       DelayMins                       = '0'
       Binding                         = 'OPTIONAL'
       MaxFailure                      = '0'
       MaxConnections                  = '0'
       ReopenSecs                      = '30'
       NetTimeout                      = '180'
       LogShipping                     = 'ON'
       PreferredApplyInstance          = ''
       ApplyInstanceTimeout            = '0'
       ApplyParallel                   = 'AUTO'
       StandbyFileManagement           = 'AUTO'
       ArchiveLagTarget                = '0'
       LogArchiveMaxProcesses          = '5'
       LogArchiveMinSucceedDest        = '1'
       DbFileNameConvert               = 'dbs/t, dbs/s2t'
       LogFileNameConvert              = 'dbs/t, dbs/s2t'
       FastStartFailoverTarget         = ''
       StatusReport                    = '(monitor)'
       InconsistentProperties          = '(monitor)'
       InconsistentLogXptProps         = '(monitor)'
       SendQEntries                    = '(monitor)'
       LogXptStatus                    = '(monitor)'
       RecvQEntries                    = '(monitor)'
       HostName                        = 'dr.foo.com'
       SidName                         = 'dr_sales1'
       LocalListenerAddress            = '(ADDRESS=(PROTOCOL=TCP)(HOST=dr.foo.com)(PORT=1514))'
       StandbyArchiveLocation          = '/archfs/arch'
       AlternateLocation               = ''
       LogArchiveTrace                 = '127'
       LogArchiveFormat                = 'db3r_%d_%t_%s_%r.arc'
       LatestLog                       = '(monitor)'
       TopWaitEvents                   = '(monitor)'

    Current status for "DR_Sales":
    SUCCESS

In particular, you should examine the current status of the database.

Step 3 Issue the switchover command.

Issue the `SWITCHOVER` command to swap the roles of the primary and standby databases. The following example shows how the broker automatically shuts down and restarts the two participating databases as a part of the switchover. (See the usage notes in Section 8.1.3 for information about how to set up the broker environment so that DGMGRL can automatically restart the primary and standby databases for you.)

    DGMGRL> SWITCHOVER TO DR_Sales;
    Performing switchover NOW, please wait...
    Operation requires shutdown of instance "sales1" on database "North_Sales"
    Shutting down instance "sales1"...
    ORA-01109: database not open

    Database dismounted.
    ORACLE instance shut down.
    Operation requires shutdown of instance "dr_sales1" on database "DR_Sales"
    Shutting down instance "dr_sales1"...
    ORA-01109: database not open

    Database dismounted.
    ORACLE instance shut down.
    Operation requires startup of instance "sales1" on database "North_Sales"
    Starting instance "sales1"...
    ORACLE instance started.
    Database mounted.
    Operation requires startup of instance "dr_sales1" on database "DR_Sales"
    Starting instance "dr_sales1"...
    ORACLE instance started.
    Database mounted.
    Switchover succeeded, new primary is "DR_Sales"

After the switchover completes, use the `SHOW CONFIGURATION` and `SHOW DATABASE` commands to verify that the switchover operation was successful.

Step 4 Show the configuration.

Issue the `SHOW CONFIGURATION` command to verify that the switchover was successful.

    DGMGRL> SHOW CONFIGURATION;

    Configuration
     Name:                DRSolution
     Enabled:             YES
     Protection Mode:     MaxProtection
     Fast-Start Failover: DISABLED
     Databases:
       North_Sales  - Physical standby database
       DR_Sales     - Primary database

    Current status for "DRSolution":
    SUCCESS

## 7.9 Scenario 8: Performing a Manual Failover Operation

You invoke a failover operation in response to an emergency situation, usually when the primary database cannot be accessed or is unavailable. See Section 5.2 before you fail over to decide which standby database should be the target of the failover. The following scenario describes a failover to the remote database called `DR_Sales`.

Note: If fast-start failover is enabled, you can perform a manual failover only to the standby database that was specified as the target of a fast-start failover and only when the observer is running. Performing a manual failover in this case can be useful if the target standby database is in the `SUSPEND` state or if the `FastStartFailoverThreshold` property is set to a very high value. In this case, first disable fast-start failover using the `FORCE` option on the target standby database before performing manual failover. See Section 5.5.4, "Disabling Fast-Start Failover" for more information about the `FORCE` option.

Step 1 Connect to the target standby database.

To perform the failover operation, you must connect to the standby database to which you want to fail over using the `SYSDBA` username and password of that database. For example:

    DGMGRL> CONNECT sys/knl_test7@DR_Sales.foo.com
    Connected.

Step 2 Issue the failover command.

Now you can issue the failover command to make the target standby database the new primary database for the configuration. Note that after the failover completes, the original primary database cannot be used as a standby database of the new primary database unless it is reenabled (as described in Section 5.4.3).

If the target of the failover is a physical standby database, the failover behavior differs depending on whether or not the physical standby database had ever been opened read-only:

- If failover occurs to a physical standby that had been opened read-only, the database must be shut down and restarted as the new primary database.

  The following example shows how the broker automatically shuts down and restarts the new primary database as a part of the failover:

      DGMGRL> FAILOVER TO "DR_Sales";
      Performing failover NOW. Please wait...
      Operation requires shutdown of instance "dr_sales1" on database
      "DR_Sales".
      Shutting down instance "dr_sales1"...
      database not mounted
      ORACLE instance shut down.
      Operation requires startup of instance "dr_sales1" on database "DR_Sales".
      Starting instance "dr_sales1"...
      ORACLE instance started.
      Database mounted.
      Failover succeeded. New primary is "DR_Sales"

  See Also: (The usage notes in Section 8.1.3 for information about how to set up the broker environment so that DGMGRL can automatically restart the new primary database

- If failover occurs to a physical standby that has never been opened read-only, then the database does not need to be restarted. For example:

      DGMGRL> FAILOVER TO 'DR_Sales';
      Performing failover NOW, please wait...
      Failover succeeded, new primary is "DR_Sales"

Step 3 Show the configuration.

Issue the `SHOW CONFIGURATION` command to verify the failover.

    DGMGRL> SHOW CONFIGURATION;

    Configuration
     Name:                DRSolution
     Enabled:             YES
     Protection Mode:     MaxPerformance
     Fast-Start Failover: DISABLED
     Databases:
       North_Sales  - Physical standby database (disabled)
       DR_Sales     - Primary database

    Current status for "DRSolution":
    SUCCESS

Step 4 Show the database.

Issue the `SHOW DATABASE` command to see that the former (failed) primary database was disabled by the broker as a consequence of the failover. It must be reenabled (as described in Section 5.4.3).

    DGMGRL> SHOW DATABASE 'North_Sales';

    Database
     Name:            North_Sales
     Role:            PHYSICAL STANDBY
     Enabled:         NO
     Intended State:  ONLINE
     Instance(s):
       sales1

    Current status for "North_Sales":
    Error: ORA-16661: the standby database needs to be reinstated

## 7.10 Scenario 9: Monitoring a Data Guard Configuration

The scenario in this section demonstrates how to use the `SHOW` command and monitorable database properties to identify and resolve a failure situation.

Step 1 Check the configuration status.

The status of the broker configuration is an aggregated status of all databases and instances in the broker configuration. You can check the configuration status first to determine whether or not any further action needs to be taken. If the configuration status is `SUCCESS`, everything in the broker configuration is working fine. However, if you see the following error, it means something is wrong in the configuration:

    DGMGRL> SHOW CONFIGURATION;
    Configuration
     Name:                DRSolution
     Enabled:             NO
     Protection Mode:     MaxPerformance
     Fast-Start Failover: DISABLED
     Databases:
       North_Sales  - Primary database
       DR_Sales     - Physical standby database

    Current status for "DRSolution":
    Warning: ORA-16607: one or more databases have failed

In this case, you need to continue on to Step 2 to determine the actual failure.

Step 2 Check the database status.

To identify which database has the failure, you need to go through all of the databases in the configuration one by one. In this example, the error happens to be on the primary database `North_Sales`:

    DGMGRL> SHOW DATABASE 'North_Sales';

The command returns the following output:

    Database
      Name:            North_Sales
      Role:            PRIMARY
      Enabled:         YES
      Intended State:  ONLINE
      Instance(s):
        sales1

    Current status for "North_Sales":
    Error: ORA-16810: multiple errors or warnings detected for the database

Step 3 Check the monitorable property StatusReport.

When you see message ORA-16810, you can use the monitorable property `StatusReport` to identify each of the errors or warnings:

    DGMGRL> SHOW DATABASE 'North_Sales' 'StatusReport';
    STATUS REPORT
           INSTANCE_NAME   SEVERITY ERROR_TEXT
                  sales1      ERROR ORA-16737: the redo transport service for
    standby "DR_Sales" has an error
                 sales1    WARNING ORA-16714: the value of property
    LogArchiveTrace is inconsistent with the database setting
                 sales1    WARNING ORA-16715: log transport related property
    ReopenSecs of standby "DR_Sales" is inconsistent

Step 4 Check the monitorable property LogXptStatus.

You see error ORA-16737 in the previous status report in Step 3. To identify the exact log transport error, you can use monitorable property `LogXptStatus`:

    DGMGRL> SHOW DATABASE 'North_Sales' 'LogXptStatus';
    LOG TRANSPORT STATUS
    PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS
                  sales1             DR_Sales ORA-12541: TNS:no listener

Now you know the exact reason why redo transport services failed. To fix this error, start the listener for the physical standby database `DR_Sales`.

Step 5 Check the monitorable property InconsistentProperties.

You also see warning ORA-16714 reported in Step 3. To identify the inconsistent values for property `LogArchiveTrace`, you can use monitorable property `InconsistentProperties`:

    DGMGRL> SHOW DATABASE 'North_Sales' 'InconsistentProperties';

    INCONSISTENT PROPERTIES
       INSTANCE_NAME   PROPERTY_NAME    MEMORY_VALUE    SPFILE_VALUE    BROKER_VALUE
              sales1   LogArchiveTrace           255            0            0

It seems that the current database memory value (255) is different from both the server parameter file (SPFILE) value (0) and Data Guard broker's property value (0). If you decide the database memory value is correct, you can update Data Guard broker's property value using the following command:

    DGMGRL> EDIT DATABASE 'North_Sales' SET PROPERTY 'LogArchiveTrace'=255;
    Property "LogArchiveTrace" updated

In the previous command, Data Guard broker also updates the spfile value for you so that value for `LogArchiveTrace` is kept consistent.

Step 6 Check the monitorable property InconsistentLogXptProps.

Another warning you see in the status report returned in Step 3 is ORA-16715. To identify the inconsistent values for the redo transport property, `ReopenSecs`, you can use monitorable property `InconsistentLogXptProps`:

    DGMGRL> SHOW DATABASE 'North_Sales' 'InconsistentLogXptProps';

    INCONSISTENT LOG TRANSPORT PROPERTIES
       INSTANCE_NAME    STANDBY_NAME   PROPERTY_NAME    MEMORY_VALUE    BROKER_VALUE
              sales1        DR_Sales      ReopenSecs             600             300

The current database memory value (600) is different from the Data Guard broker's property value (300). If you think the broker's property value is correct, you can fix the inconsistency by re-editing the property of the standby database with the same value, as shown in the following example:

    DGMGRL> EDIT DATABASE 'DR_Sales' SET PROPERTY 'ReopenSecs'=300;
    Property "ReopenSecs" updated

You can also reenable the standby database or reset the primary database state to `ONLINE` to fix the inconsistency, but re-editing the property is the simplest.